﻿
namespace HirelSeriesH.Tools
{
    class DBSql
    {
        //查询所有username
        public static readonly string SELECT_HTRB_USER_USERNAME = "SELECT UserName FROM htrb_user ORDER BY UserName ASC";
        //查询license表是否有数据
        public static readonly string COUNT_LICENSE = "SELECT COUNT(*) FROM license";
        //查询license表所有数据
        public static readonly string SELECT_LICENSE = "SELECT * FROM license";
        //建立license记录
        public static readonly string INSERT_LICENSE = "INSERT INTO license(StartTime,RunMilliSecond,RunDays) VALUES(@StartTime,@RunMilliSecond,@RunDays)";
        //更新license的RunMilliSecond
        public static readonly string UPDATE_LICENSE_RMS = "UPDATE license SET RunMilliSecond=@RunMilliSecond";
        //更新license
        public static readonly string UPDATE_LICENSE = "UPDATE license SET RunMilliSecond=@RunMilliSecond,RunDays=@RunDays";

        //查询htrb_box表是否有数据 
        public static readonly string COUNT_HTRB_BOX = "SELECT COUNT(ID) FROM htrb_box";
        //建立温箱记录
        public static readonly string INSERT_HTRB_BOX = "INSERT INTO htrb_box(ID,ControlModel,BeforeTemperature,BeforeTime,Temperature,Shidu,AfterTemperature,AfterTime,CloseTemperature,StepNum) VALUES(@ID,@ControlModel,@BeforeTemperature,@Temperature,@Shidu,@AfterTemperature,@CloseTemperature,@StepNum)";

        public static readonly string SELECT_HTRB_BOX = "SELECT * FROM htrb_box WHERE ControlModel=@ControlModel AND BoxNo=@BoxNo";

        //建立温度阶段控制初始记录
        public static readonly string INSERT_HTRB_BOX_ORDER = "INSERT INTO htrb_box_order(ID,Step,Temperature,Duration,CheckInterval) VALUES(@ID,@Step,'0','0','0')";
        //查询htrb_box_Temperature
        public static readonly string SELECT_HTRB_BOX_TEMPERATURE = "SELECT Temperature FROM htrb_box WHERE ControlModel=@ControlModel";
        public static readonly string SELECT_HTRB_BOX_TEMPERATURE_SHIDU = "SELECT Shidu FROM htrb_box WHERE ControlModel=@ControlModel";
        //更新温箱设定的温度(定值模式启动时)
        public static readonly string UPDATE_HTRB_BOX_VALUE_TEMPERATURE = "UPDATE htrb_box SET BeforeTemperature=@BeforeTemperature,BeforeTime=@BeforeTime,Temperature=@Temperature,Shidu=@Shidu,AfterTemperature=@AfterTemperature,AfterTime=@AfterTime,CloseTemperature=@CloseTemperature WHERE ControlModel=@ControlModel";
        //更新温箱的温度(程序模式启动时)
        public static readonly string UPDATE_HTRB_BOX_STEP_STEPNUM = "UPDATE htrb_box SET StepNum=@StepNum WHERE ControlModel=@ControlModel";
        //查询htrb_box_StepNum
        public static readonly string SELECT_HTRB_BOX_STEPNUM = "SELECT StepNum FROM htrb_box WHERE ControlModel=@ControlModel";
        //查询htrb_box_order表所有数据
        public static readonly string SELECT_HTRB_BOX_ORDER = "SELECT * FROM htrb_box_order";
        //更新htrb_box_order
        public static readonly string UPDATE_HTRB_BOX_ORDER = "UPDATE htrb_box_order SET Temperature=@Temperature,Duration=@Duration,CheckInterval=@CheckInterval WHERE Step=@Step";

        public static readonly string UPDATE_HTRB_BOX_VALUE_TEMPERATURE_NEW = "UPDATE htrb_box SET Temperature=@Temperature,Humidity=@Humidity,Hour=@HOURS,Min=@MINUTES,Post=@POSTM WHERE ControlModel='value' AND BoxNo=@BOXNO";

        //查询指定角色用户是否存在
        public static readonly string SELECT_SUPERADMIN_USER = "SELECT count(htrb_user.ID) FROM htrb_user,htrb_user_role,htrb_role WHERE htrb_user.ID=htrb_user_role.UserID AND htrb_user_role.RoleID=htrb_role.ID AND htrb_user.UserName=@UserName AND htrb_user.PassWord=@PassWord AND htrb_role.RoleName=@RoleName";
        //查询超级管理员或者工程师用户
        public static readonly string SELECT_SUPERADMIN_ENGINEER_USER = "SELECT count(htrb_user.ID) FROM htrb_user,htrb_user_role,htrb_role WHERE htrb_user.ID=htrb_user_role.UserID AND htrb_user_role.RoleID=htrb_role.ID AND htrb_user.UserName=@UserName AND htrb_user.PassWord=@PassWord AND htrb_role.RoleName='SuperAdmin' or htrb_role.RoleName='Engineer'";
        //查询用户名是否已被占用
        public static readonly string SELECT_USERNAME = "SELECT count(htrb_user.ID) FROM htrb_user WHERE htrb_user.UserName=@UserName";
        //新建工程师用户
        public static readonly string INSERT_HTRB_USER = "INSERT INTO htrb_user(ID,UserName,PassWord,CreateBy,CreateDate,UpdateBy,UpdateDate) VALUES(@ID,@UserName,@PassWord,@CreateBy,@CreateDate,@UpdateBy,@UpdateDate)";
        //给用户分配工程师角色
        public static readonly string INSERT_HTRB_USER_ROLE = "INSERT INTO htrb_user_role(ID,RoleID,UserID,CreateBy,CreateDate,UpdateBy,UpdateDate) VALUES(@ID,@RoleID,@UserID,@CreateBy,@CreateDate,@UpdateBy,@UpdateDate)";
        //查用工程师角色
        public static readonly string SELECT_HTRB_ROLE_ENGINEER = "SELECT ID FROM htrb_role WHERE RoleName='Engineer'";
        //查询所有工程师用户
        public static readonly string SELECT_ENGINEER_USER = "SELECT htrb_user.ID userID,htrb_user_role.ID roleID,htrb_user.UserName,htrb_role.RoleName,htrb_user.CreateBy,htrb_user.CreateDate FROM htrb_user,htrb_user_role,htrb_role WHERE htrb_user.ID=htrb_user_role.UserID AND htrb_user_role.RoleID=htrb_role.ID AND htrb_role.RoleName='Engineer' ORDER BY htrb_user.CreateDate DESC";
        //删除用户角色
        public static readonly string DELE_HTRB_USER_ROLE_ENGINEER = "DELETE FROM htrb_user_role WHERE UserID = @UserID AND RoleID = @RoleID";
        //删除用户
        public static readonly string DELE_HTRB_ENGINEER_USER = "DELETE FROM htrb_user WHERE ID = @ID";
        //查询指定用户是否存在
        public static readonly string SELECT_HTRB_USER = "SELECT ID FROM htrb_user WHERE UserName=@UserName AND PassWord=@PassWord";
        //更新用户登录密码
        public static readonly string UPDATE_HTRB_USER_PASSWORD = "UPDATE htrb_user SET PassWord=@PassWord WHERE ID=@ID";



        //新建测试参数方案
        public static readonly string INSERT_HTRB_TEST_PARAMETER = "INSERT INTO htrb_test_parameter(ID,ParaName,Remarks,ProductModel,BatchNumber,CheckInterval,PowerStepConfig,HighTemp,LowTemp,HighTime,LowTime,IsLineContrl,XieLv,TestCycs,StratPos,TempContrl,BeforTemp,BeforTime,AfterTemp,AfterTime,Ysfz,CreateBy,CreateDate,UpdateBy,UpdateDate,isStatic) VALUES(@ID,@ParaName,@Remarks,@ProductModel,@BatchNumber,@CheckInterval,@PowerStepConfig,@HighTemp,@LowTemp,@HighTime,@LowTime,@IsLineContrl,@XieLv,@TestCycs,@StratPos,@TempContrl,@BeforTemp,@BeforTime,@AfterTemp,@AfterTime,@Ysfz,@CreateBy,@CreateDate,@UpdateBy,@UpdateDate,@isStatic)";
        //更新测试参数方案
        public static readonly string UPDATE_HTRB_TEST_PARAMETER = "UPDATE htrb_test_parameter SET ParaName=@ParaName,Remarks=@Remarks,ProductModel=@ProductModel,BatchNumber=@BatchNumber,CheckInterval=@CheckInterval,PowerStepConfig=@PowerStepConfig,HighTemp=@HighTemp,LowTemp=@LowTemp,HighTime=@HighTime,LowTime=@LowTime,IsLineContrl=@IsLineContrl,XieLv=@XieLv,TestCycs=@TestCycs,StratPos=@StratPos,TempContrl=@TempContrl,BeforTemp=@BeforTemp,BeforTime=@BeforTime,AfterTemp=@AfterTemp,AfterTime=@AfterTime,Ysfz=@Ysfz,UpdateBy=@UpdateBy,UpdateDate=@UpdateDate,isStatic=@isStatic WHERE ID=@ID";



        //删除测试参数方案
        public static readonly string DELE_HTRB_TEST_PARAMETERR = "DELETE FROM htrb_test_parameter WHERE ID = @ID";
        //查询测试参数方案
        public static readonly string SELECT_HTRB_TEST_PARAMETERR = "SELECT * FROM htrb_test_parameter ORDER BY CreateDate DESC";
        //按条件查询测试参数方案
        public static readonly string SELECT_HTRB_TEST_PARAMETERR_CRITERIA = "SELECT * FROM htrb_test_parameter WHERE ParaName like @ParaName ORDER BY CreateDate DESC";
        //查询htrb_power表是否有数据
        public static readonly string COUNT_HTRB_POWER = "SELECT COUNT(ID) FROM htrb_power";
        //建立电源记录
        public static readonly string INSERT_HTRB_POWER = "INSERT INTO htrb_power(ID,PowerName,Voltage) VALUES(@ID,@PowerName,@Voltage)";
        //查询htrb_channel表是否有数据
        public static readonly string COUNT_HTRB_CHANNEL = "SELECT COUNT(ID) FROM htrb_channel";
        //建立老化板记录
        public static readonly string INSERT_HTRB_CHANNEL = "INSERT INTO htrb_channel(ID,ChannelName,PowerID) VALUES(@ID,@ChannelName,@PowerID)";
        //建立老化板工位记录
        public static readonly string INSERT_HTRB_CHANNEL_POSITIONS = "INSERT INTO htrb_positions(ID,ChID,ChannelName,PosIndex,BasicValue) VALUES(@ID,@ChID,@ChannelName,@PosIndex,@BasicValue)";
        public static readonly string SELECT_HTRB_CHANNEL_POSITIONS = "SELECT * FROM htrb_positions WHERE ChID=@ChID";
        public static readonly string UPDATE_HTRB_CHANNEL_POSITIONS = "UPDATE htrb_positions SET BasicValue=@BasicValue WHERE ID=@ID";
        //查询htrb_power表所有记录
        public static readonly string SELECT_HTRB_POWER = "SELECT * FROM htrb_power";
        //已ID为条件查询htrb_power表记录
        public static readonly string SELECT_HTRB_POWER_BY_ID = "SELECT * FROM htrb_power WHERE ID = @ID";
        //更新电源的升降压设置以及监控周期设置
        public static readonly string UPDATE_HTRB_POWERT_UPDOWN = "UPDATE htrb_power SET UpStep=@UpStep,UpInterval=@UpInterval,DownStep=@DownStep,DownInterval=@DownInterval,CheckInterval=@CheckInterval WHERE ID=@ID";
        //更新电源的全部设置 
        public static readonly string UPDATE_HTRB_POWERT_ALL = "UPDATE htrb_power SET Voltage=@Voltage,UpStep=@UpStep,UpInterval=@UpInterval,DownStep=@DownStep,DownInterval=@DownInterval,CheckInterval=@CheckInterval WHERE ID=@ID";
        //查询htrb_box_order表所有数据
        public static readonly string SELECT_HTRB_CHANNEL = "SELECT * FROM htrb_channel";
        //更老化板的供电电源信息
        public static readonly string UPDATE_HTRB_CHANNEL_POWERID = "UPDATE htrb_channel SET PowerID=@PowerID WHERE ID=@ID";
        //更老化板的参数校正信息
        public static readonly string UPDATE_HTRB_CHANNEL_CORRECT = "UPDATE htrb_channel SET BHDZ=@BHDZ, CYDZ=@CYDZ, GWJZ_K=@GWJZ_K, GWJZ_B=@GWJZ_B, GWJZ1_K=@GWJZ1_K, GWJZ1_B=@GWJZ1_B, GWJZ10_K=@GWJZ10_K, GWJZ10_B=@GWJZ10_B, GWJZ100_K=@GWJZ100_K, GWJZ100_B=@GWJZ100_B, GWJZ1000_K=@GWJZ1000_K, GWJZ1000_B=@GWJZ1000_B, DLYZ=@DLYZ, ToInit=@ToInit WHERE ID=@ID";
        //更新电源的参数校正信息
        /*public static readonly string UPDATE_HTRB_POWERT_CORRECT = "UPDATE htrb_power SET ShowK=@ShowK,ShowB=@ShowB,OutK=@OutK,OutB=@OutB,BHDZ=@BHDZ WHERE ID=@ID";*/
        public static readonly string UPDATE_HTRB_POWERT_CORRECT = "UPDATE htrb_power SET BHDZ=@BHDZ WHERE ID=@ID";
        //更新电源的出厂设置信息
        public static readonly string  UPDATE_HTRB_POWER_SETTING = "UPDATE htrb_power SET MaxVoltage=@MaxVoltage,MaxCurrent=@MaxCurrent,BHDZ=@BHDZ WHERE ID=@ID";

        //更新电源的全部设置 
        public static readonly string UPDATE_HTRB_POWER_ALL = "UPDATE htrb_power SET Voltage=@Voltage,UpStep=@UpStep,UpInterval=@UpInterval,DownStep=@DownStep,DownInterval=@DownInterval,CheckInterval=@CheckInterval WHERE ID=@ID";
        public static readonly string UPDATE_HTRB_POWER_UPDOWN = "UPDATE htrb_power SET UpStep=@UpStep,UpInterval=@UpInterval,DownStep=@DownStep,DownInterval=@DownInterval,CheckInterval=@CheckInterval WHERE ID=@ID";

        public static readonly string SELECT_HTRB_POSITIONS_JZ = "SELECT * FROM htrb_positions_jz";

        public static readonly string UPDATE_HTRB_POSITIONS_JZ = "UPDATE htrb_positions_jz SET KK=@KK, BB=@BB WHERE ID=@ID";



        public static readonly string SELECT_PTC_BOX = "SELECT * FROM ptc_box";
        public static readonly string UPDATE_PTC_BOX = "UPDATE ptc_box SET HighTemp=@HighTemp, LowTemp=@LowTemp, HighTime=@HighTime, LowTime=@LowTime, IsLineContrl=@IsLineContrl, XieLv=@XieLv, TestCycs=@TestCycs, StratPos=@StratPos, TempContrl=@TempContrl, BeforTemp=@BeforTemp, BeforTime=@BeforTime, AfterTemp=@AfterTemp, AfterTime=@AfterTime, Ysfz=@Ysfz WHERE ID=@ID";


        public static readonly string UPDATE_HTRB_POWER_ONOFF = "UPDATE htrb_power SET Voltage=@Voltage,OnTime=@OnTime,OffTime=@OffTime WHERE ID=@ID";
    }
}
